﻿'Copyright ?1995-2007, Camstar Systems, Inc. All Rights Reserved.
'Description:委托计量访问类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:tianFW
'create Date:2017-8-11
Imports BusinessRule.IBusiness
Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports System.Text
Imports System.Data.OracleClient
Imports External.APICalling

Public Class uLMSEntrustBusiness
    Implements uLMSEntrustIBusiness

    ''' <summary>
    ''' 添加委托计量信息
    ''' </summary>
    ''' <param name="dto"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function AddEntrustMeteringInfo(ByVal dto As EntrustMeteringDTO) As Boolean Implements IBusiness.uLMSEntrustIBusiness.AddEntrustMeteringInfo
        Dim strsql As String =
            <string>
                INSERT INTO ENTRUSTMEASUREMENT(CONTAINERID,WORKFLOWSTEPID,CREATEDATE,ENTRUSTDATE,RESERVEDATE,
                ENTRUSTMEASUREMENTID,ENTRUSTNAME,ENTRUSTNOTE,ENTRUSTORID,ENTRUSTQTY,FACTORYID,PRODUCTID,ISCHECKDETECTION,ENTRUSCHECKTYPE,IsPhy,BatchNO,Specification)
                VALUES(:CONTAINERID,:WORKFLOWSTEPID,SYSDATE,:ENTRUSTDATE,:RESERVEDATE,:ENTRUSTMEASUREMENTID,:ENTRUSTNAME,:ENTRUSTNOTE,:ENTRUSTORID,:ENTRUSTQTY,:FACTORYID,:PRODUCTID,0,:ENTRUSCHECKTYPE,:IsPhy,:BatchNO,:Specification)
            </string>
        Dim insertpara(14) As OracleParameter
        insertpara(0) = DatabaseHelper.NewParameter(":CONTAINERID", dto.ContainerID, OracleType.VarChar) 'modify 2017/10/10
        insertpara(1) = DatabaseHelper.NewParameter(":WORKFLOWSTEPID", dto.WorkflowStepID, OracleType.VarChar)
        If dto.EntrustDate <> DateTime.Parse("0001-01-01") Then
            insertpara(2) = DatabaseHelper.NewParameter(":ENTRUSTDATE", dto.EntrustDate, OracleType.DateTime)
        Else
            insertpara(2) = DatabaseHelper.NewParameter(":ENTRUSTDATE", DBNull.Value, OracleType.DateTime)
        End If
        If dto.ReserveDate <> DateTime.Parse("0001-01-01") Then
            insertpara(3) = DatabaseHelper.NewParameter(":RESERVEDATE", dto.ReserveDate, OracleType.DateTime)
        Else
            insertpara(3) = DatabaseHelper.NewParameter(":RESERVEDATE", DBNull.Value, OracleType.DateTime)
        End If
        insertpara(4) = DatabaseHelper.NewParameter(":ENTRUSTMEASUREMENTID", dto.EntrustMeasurementID, OracleType.VarChar)
        insertpara(5) = DatabaseHelper.NewParameter(":ENTRUSTNAME", dto.EntrustName, OracleType.VarChar)
        insertpara(6) = DatabaseHelper.NewParameter(":ENTRUSTNOTE", dto.EntrustNote, OracleType.VarChar)
        insertpara(7) = DatabaseHelper.NewParameter(":ENTRUSTORID", dto.EntrustorID, OracleType.VarChar)
        insertpara(8) = DatabaseHelper.NewParameter(":ENTRUSTQTY", dto.EntrustQty, OracleType.Int32)
        insertpara(9) = DatabaseHelper.NewParameter(":FACTORYID", dto.FactoryID, OracleType.VarChar)
        insertpara(10) = DatabaseHelper.NewParameter(":PRODUCTID", dto.ProductID, OracleType.VarChar)
        insertpara(11) = DatabaseHelper.NewParameter(":ENTRUSCHECKTYPE", dto.EntrusChecktype, OracleType.Number)
        insertpara(12) = DatabaseHelper.NewParameter(":IsPhy", dto.IsPhy, OracleType.Number)
        insertpara(13) = DatabaseHelper.NewParameter(":BatchNO", dto.BatchNO, OracleType.VarChar)
        insertpara(14) = DatabaseHelper.NewParameter(":Specification", dto.Specification, OracleType.VarChar)
        If DatabaseHelper.ExecuteSql(strsql, insertpara) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 更改委托计量信息
    ''' </summary>
    ''' <param name="dto"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateOldEntrustMeteringInfo(ByVal dto As EntrustMeteringDTO) As Boolean Implements IBusiness.uLMSEntrustIBusiness.UpdateOldEntrustMeteringInfo
        Dim strsql As String = "" 'String.Format("DELETE FROM ENTRUSTMEASUREMENTDETAIL EN WHERE EN.ENTRUSTMEASUREMENTID='{0}'", dto.EntrustMeasurementID)
            strsql =
                <string>
                    UPDATE ENTRUSTMEASUREMENT EN SET EN.ENTRUSTDATE=:ENTRUSTDATE,EN.RESERVEDATE=:RESERVEDATE,EN.ENTRUSTNOTE=:ENTRUSTNOTE,
                    EN.ENTRUSTQTY=:ENTRUSTQTY WHERE EN.ENTRUSTMEASUREMENTID=:ENTRUSTMEASUREMENTID
                </string>
            Dim insertpara(4) As OracleParameter
            insertpara(0) = DatabaseHelper.NewParameter(":ENTRUSTDATE", dto.EntrustDate, OracleType.DateTime)
            insertpara(1) = DatabaseHelper.NewParameter(":RESERVEDATE", dto.ReserveDate, OracleType.DateTime)
            insertpara(2) = DatabaseHelper.NewParameter(":ENTRUSTNOTE", dto.EntrustNote, OracleType.VarChar)
            insertpara(3) = DatabaseHelper.NewParameter(":ENTRUSTQTY", dto.EntrustQty, OracleType.Int32)
            insertpara(4) = DatabaseHelper.NewParameter(":ENTRUSTMEASUREMENTID", dto.EntrustMeasurementID, OracleType.VarChar)
            If DatabaseHelper.ExecuteSql(strsql, insertpara) > 0 Then
                Return True
            Else
                Return False
            End If
        Return False
    End Function

    ''' <summary>
    ''' 添加委托计量详细信息
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function AddAddEntrustMeteringDetailInfo(ByVal dto As EntrustMeteringDTO) As Boolean Implements IBusiness.uLMSEntrustIBusiness.AddAddEntrustMeteringDetailInfo
        Dim strsql As String =
            <string>
                INSERT INTO ENTRUSTMEASUREMENTDETAIL(ENTRUSTMEASUREMENTID, DETAILNO, CHECKPOINTNOTE,CHECKPOINTNAME,DRAWINGNO)
                VALUES(:ENTRUSTMEASUREMENTID, :DETAILNO, :CHECKPOINTNOTE,:CHECKPOINTNAME,:DRAWINGNO)
            </string>
        Dim insertpara(4) As OracleParameter
        insertpara(0) = DatabaseHelper.NewParameter(":ENTRUSTMEASUREMENTID", dto.EntrustMeasurementID, OracleType.VarChar)
        insertpara(1) = DatabaseHelper.NewParameter(":DETAILNO", dto.DetailNo, OracleType.Int32)
        insertpara(2) = DatabaseHelper.NewParameter(":CHECKPOINTNOTE", dto.CheckPointNote, OracleType.VarChar)
        insertpara(3) = DatabaseHelper.NewParameter(":CHECKPOINTNAME", dto.CheckPointName, OracleType.VarChar)
        insertpara(4) = DatabaseHelper.NewParameter(":DRAWINGNO", dto.DrawingNO, OracleType.VarChar)
        If DatabaseHelper.ExecuteSql(strsql, insertpara) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 获取委托计量最大值得单号
    ''' </summary>
    ''' <param name="EntrustName_"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetMaxEntrustName(ByVal EntrustName_ As String) As DataTable Implements IBusiness.uLMSEntrustIBusiness.GetMaxEntrustName
        Dim strsql As String = String.Format("SELECT MAX(EM.ENTRUSTNAME) ENTRUSTNAME FROM ENTRUSTMEASUREMENT EM WHERE EM.ENTRUSTNAME LIKE '{0}%'", EntrustName_)
        Return DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function

    ''' <summary>
    ''' 获取需要检测或者修改的委托计量信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetEntrustMeteringInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements IBusiness.uLMSEntrustIBusiness.GetEntrustMeteringInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT C.CONTAINERNAME,EM.CONTAINERID,EM.WORKFLOWSTEPID,EE.FULLNAME ENTRUSTORNAME,EEE.FULLNAME TREATERNAME,EM.ENTRUSTMEASUREMENTID,EM.ENTRUSTNAME,EM.ENTRUSTDATE,EM.TREATNOTE,")
        strsql.AppendLine("EM.RESERVEDATE,DECODE(EM.ISCHECKDETECTION,1,'是','否') ISCHECKDETECTION,PB.PRODUCTNAME,P.DESCRIPTION PRODUCTDESC,C.QTY CONTAINERQTY,EM.ENTRUSTQTY,EM.ENTRUSTNOTE,F.FACTORYNAME,WS.WORKFLOWSTEPNAME ")
        strsql.AppendLine(",decode(em.entruschecktype,1,'特种检验',2,'无损检验',3,'理化检验') entruschecktype ")
        If para.Keys.Contains("Current") Then
            strsql.AppendLine(",CO.WORKREPORTNUM")
        End If
        strsql.AppendLine("FROM ENTRUSTMEASUREMENT EM LEFT JOIN CONTAINER C ON C.CONTAINERID = EM.CONTAINERID")
        If para.Keys.Contains("Current") Then
            strsql.AppendLine("LEFT JOIN CONTAINERCURRENTINFO CO ON CO.CONTAINERID = EM.CONTAINERID")
        End If
        strsql.AppendLine("LEFT JOIN PRODUCT P ON P.PRODUCTID = EM.PRODUCTID")
        strsql.AppendLine("LEFT JOIN PRODUCTBASE PB ON PB.PRODUCTBASEID = P.PRODUCTBASEID")
        strsql.AppendLine("LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = EM.WORKFLOWSTEPID")
        strsql.AppendLine("LEFT JOIN SPECBASE SB ON SB.SPECBASEID = WS.SPECBASEID")
        strsql.AppendLine("LEFT JOIN SPEC S ON S.SPECID = NVL(SB.REVOFRCDID, WS.SPECID)")
        strsql.AppendLine("LEFT JOIN FACTORY F ON F.FACTORYID = EM.FACTORYID")
        strsql.AppendLine("LEFT JOIN EMPLOYEE EE ON EE.EMPLOYEEID = EM.ENTRUSTORID")
        strsql.AppendLine("LEFT JOIN EMPLOYEE EEE ON EEE.EMPLOYEEID = EM.TREATEMPLOYEEID")
        strsql.AppendLine(" WHERE CO.CONTAINERSTATUS=1 ") 'MODIFY 2017/11/02 C.STATUS=1
        If para.Keys.Contains("Current") Then
            strsql.AppendLine(" AND CO.WORKFLOWSTEPID=EM.WORKFLOWSTEPID")
        End If
        If para.Keys.Contains("ScanContainerName") Then
            strsql.AppendFormat(" AND C.CONTAINERNAME = '{0}'", para("ScanContainerName"))
        Else
            If para.Keys.Contains("ContainerName") Then
                strsql.AppendFormat(" AND C.CONTAINERNAME LIKE '%{0}%'", para("ContainerName"))
            End If
        End If
        If para.Keys.Contains("ProcessNO") Then
            strsql.AppendFormat(" AND S.SPECNO LIKE '%{0}%'", para("ProcessNO"))
        End If
        If para.Keys.Contains("ProductName") Then
            strsql.AppendFormat(" AND PB.PRODUCTNAME LIKE '%{0}%'", para("ProductName"))
        End If
        If para("Status") = "1" Then '未检测的外委数据
            strsql.AppendLine(" AND EM.TREATEMPLOYEEID IS NULL")
        ElseIf para("Status") = "2" Then '已检测的外委数据
            strsql.AppendLine(" AND EM.TREATEMPLOYEEID IS NOT NULL")
        End If
        strsql.AppendFormat(" AND EM.FACTORYID='{0}'", para("FactoryID"))
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strsql.ToString
        Dim result As uMESProcessResult = DatabaseHelper.RunProcedureForPage(paraPage)
        Return result
    End Function

    ''' <summary>
    ''' 根据委托计量ID获取委托计量详细信息
    ''' </summary>
    ''' <param name="EntrustMeasurementID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetEntrustMeteringDetailInfo(ByVal EntrustMeasurementID As String) As DataTable Implements IBusiness.uLMSEntrustIBusiness.GetEntrustMeteringDetailInfo
        Dim strsql As String = String.Format("SELECT * FROM  ENTRUSTMEASUREMENTDETAIL E WHERE E.ENTRUSTMEASUREMENTID='{0}' ORDER BY E.DETAILNO", EntrustMeasurementID)
        Return DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function

    ''' <summary>
    ''' 添加委托计量附件
    ''' </summary>
    ''' <param name="model"></param>
    ''' <param name="result"></param>
    ''' <param name="message"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function AddDelegateAttachment(ByVal model As bjDelegateCheckDTO, ByRef result As Boolean, ByRef message As String) As Boolean Implements IBusiness.uLMSEntrustIBusiness.AddDelegateAttachment
        AddDelegateAttachment = False
        result = False
        Try
            Dim strInfo As String = ""
            Dim success As Boolean = False
            Dim daServerHost As ServerHostInfo = model.ServerHostInfo

            Dim strTxnDocName, strTxnName As String
            strTxnDocName = "DelegateCheckMaintDoc"
            strTxnName = "DelegateCheckMaint"

            Dim dataEntityList As New List(Of ClientAPIEntity)
            Dim dataEntity As New ClientAPIEntity

            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "DocUrl1"
            dataEntity.ClientDataValue = model.DocUrl1
            dataEntityList.Add(dataEntity)


            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "ReturnEmployee"
            dataEntity.ClientDataValue = model.ReturnEmployee
            dataEntityList.Add(dataEntity)

            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details
            dataEntity.ClientDataName = "State"
            dataEntity.ClientDataValue = model.State
            dataEntityList.Add(dataEntity)

            Dim serverUser As String = daServerHost.UserName
            Dim serverPassword As String = daServerHost.Password

            Dim clientData As New CamstarClientAPI(serverUser, serverPassword)
            success = clientData.UpdateModelByStandard(strTxnDocName, strTxnName, model.Name, "", dataEntityList, strInfo)
            If success = True Then
                AddDelegateAttachment = True
                result = True
            End If
            message = strInfo
        Catch ex As Exception
            result = False
            message = ex.Message
            Return False
        End Try
    End Function

    ''' <summary>
    ''' 更新委托计量检测信息
    ''' </summary>
    ''' <param name="dto"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateEntrustMeteringInfo(ByVal dto As EntrustMeteringDTO) As Boolean Implements IBusiness.uLMSEntrustIBusiness.UpdateEntrustMeteringInfo
        Dim strsql As New StringBuilder
        strsql.AppendFormat("UPDATE ENTRUSTMEASUREMENT EN SET EN.TREATEMPLOYEEID = '{0}',EN.TREATDATE= SYSDATE,EN.TREATNOTE= '{1}',", dto.TreatEmployeeID, dto.TreatNote)
        strsql.AppendFormat(" EN.DOCUMENTSETID= '{0}' WHERE EN.ENTRUSTMEASUREMENTID = '{1}'", dto.DocumentSetID, dto.EntrustMeasurementID)
        If DatabaseHelper.ExecuteSql(strsql.ToString) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 更新委托计量检测详细信息
    ''' </summary>
    ''' <param name="dto"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateEntrustMeteringDetailInfo(ByVal dto As EntrustMeteringDTO) As Boolean Implements IBusiness.uLMSEntrustIBusiness.UpdateEntrustMeteringDetailInfo
        Dim strsql As New StringBuilder
        strsql.AppendFormat("UPDATE ENTRUSTMEASUREMENTDETAIL EN SET EN.CHECKPOINTRESULTNOTE = '{0}'", dto.CheckPointResultNote)
        strsql.AppendFormat(" WHERE EN.ENTRUSTMEASUREMENTID = '{0}' AND EN.DETAILNO = '{1}'", dto.EntrustMeasurementID, dto.DetailNo)
        If DatabaseHelper.ExecuteSql(strsql.ToString) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 删除委托计量信息
    ''' </summary>
    ''' <param name="EntrustMeasurementID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function DelEntrustMeteringAllInfo(ByVal EntrustMeasurementID As String) As Boolean Implements IBusiness.uLMSEntrustIBusiness.DelEntrustMeteringAllInfo
        Dim strsql As String = String.Format("DELETE FROM ENTRUSTMEASUREMENT EN WHERE EN.ENTRUSTMEASUREMENTID='{0}'", EntrustMeasurementID)
        If DatabaseHelper.ExecuteSql(strsql) > 0 Then
            strsql = String.Format("DELETE FROM ENTRUSTMEASUREMENTDETAIL EN WHERE EN.ENTRUSTMEASUREMENTID='{0}'", EntrustMeasurementID)
            DatabaseHelper.ExecuteSql(strsql)
                Return True
            Else
                Return False
            End If
    End Function

    ''' <summary>
    ''' 删除委托计量详细信息
    ''' </summary>
    ''' <param name="EntrustMeasurementID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function DelDelEntrustMeteringDetailInfo(ByVal EntrustMeasurementID As String) As Boolean Implements IBusiness.uLMSEntrustIBusiness.DelDelEntrustMeteringDetailInfo
        Dim strsql As String = String.Format("DELETE FROM ENTRUSTMEASUREMENTDETAIL EN WHERE EN.ENTRUSTMEASUREMENTID='{0}'", EntrustMeasurementID)
        If DatabaseHelper.ExecuteSql(strsql) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 获取批次当前序工序已经委托的数量
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetEntrustMeteringedQty(ByVal para As Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSEntrustIBusiness.GetEntrustMeteringedQty
        Dim strsql As String = String.Format(" SELECT SUM(EN.ENTRUSTQTY) SUMQTY FROM ENTRUSTMEASUREMENT EN WHERE EN.CONTAINERID='{0}' AND EN.WORKFLOWSTEPID='{1}'", para("ContainerID"), para("WorkflowStepID"))
        Return DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function

    ''' <summary>
    ''' 确认检测
    ''' </summary>
    ''' <param name="EntrustMeasurementID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateIsCheckDetection(ByVal EntrustMeasurementID As String) As Boolean Implements IBusiness.uLMSEntrustIBusiness.UpdateIsCheckDetection
        Dim strsql As String = String.Format("UPDATE ENTRUSTMEASUREMENT EN SET EN.ISCHECKDETECTION=1 WHERE EN.ENTRUSTMEASUREMENTID='{0}'", EntrustMeasurementID)
        If DatabaseHelper.ExecuteSql(strsql) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function


    '装试专用


End Class
